package com.dcy.znoa.common.util.excel;

import com.dcy.znoa.common.util.DateTimeUtil;
import org.apache.poi.ss.usermodel.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

/**
 * Created by pengyu on 2017/6/3.
 */
public abstract class IoExcel<T> {

    /**
     * 错误信息
     */
    public String errorMessage = "";

    /**
     * 读取 List
     *
     * @param list      泛型数据
     * @param workbook  工作薄
     * @param sheetName 工作薄名称
     * @param sheetNum  工作薄数量
     * @param totalRow  总行数
     */
    protected void readListToExcel(List<T> list, Workbook workbook, String sheetName, long sheetNum, long totalRow) {
        //读取表头信息
        T t = list.get(0);
        Field[] fields = t.getClass().getDeclaredFields();
        Method[] methods = t.getClass().getMethods();
        List<String> listHeader = readListHeader(fields);

        for (int i = 0; i < sheetNum; i++) {
            Stream<T> limitList = list
                    .stream()
                    .skip(i * totalRow)
                    .limit(totalRow);
            //创建工作薄
            Sheet sheet = workbook.createSheet(sheetName + i);
            //创建工作薄标题【即第一行】
            Row row = sheet.createRow(0);
            for (int c = 0; c < listHeader.size(); c++) {
                Cell cell = row.createCell(c);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(listHeader.get(c));
                sheet.autoSizeColumn(c);
            }
            //创建标题
            final int[] rowNum = {1};
            limitList.forEach(e -> {
                Field[] fieldsEach = e.getClass().getDeclaredFields();
                Row rowField = sheet.createRow(rowNum[0]);
                int len = 0;
                for (Field field :
                        fieldsEach) {
                    FieldMeta meta = field.getAnnotation(FieldMeta.class);
                    //读取说明并验证是否为空  为空则不输出
                    String des = meta.description();
                    if (des == null || des.isEmpty())
                        continue;
                    //读取字段名
                    String name = field.getName();
                    //获取 method
                    String methodName = "get" + name.toLowerCase();
                    Optional<Method> optional = Arrays.stream(methods)
                            .filter(w -> w.getName().toLowerCase().equals(methodName))
                            .findFirst();
                    //验证是否反回空
                    if (!optional.isPresent())
                        continue;
                    //获取值
                    Method m = optional.get();
                    if (m == null)
                        continue;
                    //获取字段类型
                    Type type = field.getGenericType();
                    //获取值
                    String value = getModelValue(type, m, e);
                    Cell cell = rowField.createCell(len);

                    //设置例的值
                    setCellDataType(cell, type, value);
                    //记录当前行的例
                    sheet.autoSizeColumn(len);
                    len += 1;
                }

                rowNum[0] += 1;
                //获取该行是否有例 如果没有将其它删除
                int cellNum = rowField.getLastCellNum();
                if (cellNum == 0)
                    sheet.removeRow(rowField);
            });
        }
    }

    /**
     * 读取 excel
     *
     * @param t     实体
     * @param sheet xls or xlsx 数据
     * @return
     */
    protected List<T> readExcelToList(T t, Sheet sheet) {
        List<T> list = new ArrayList();
        //验证是否有为空的数据
        if (sheet == null)
            return list;
        try {
            int rowNum = sheet.getLastRowNum();
            if (rowNum < 1)
                return list;
            Row headerRow = sheet.getRow(0);
            List<String> headerList = readExcelHeader(headerRow);

            Field[] fields = t.getClass().getDeclaredFields();
            Method[] methods = t.getClass().getMethods();
            for (int i = 1; i <= rowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null || row.getLastCellNum() == 0)
                    continue;
                for (Field field :
                        fields) {
                    //数据类型
                    FieldMeta meta = field.getAnnotation(FieldMeta.class);
                    String title = meta.description();
                    String name = field.getName();

                    //获取 method
                    String methodName = "set" + name.toLowerCase();
                    Optional<Method> optional = Arrays.stream(methods)
                            .filter(w -> w.getName().toLowerCase().equals(methodName))
                            .findFirst();
                    //验证是否反回空
                    if (!optional.isPresent())
                        continue;
                    //获取值
                    Method m = optional.get();
                    if (m == null)
                        continue;

                    String value = "";
                    Type type = field.getGenericType();
                    int idx = headerList.indexOf(title);
                    if (idx > -1) {
                        Cell cell = row.getCell(idx);
                        value = (String) readCellDataType(cell);
                    }
                    setModelValue(type, m, t, name, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception ex) {
            errorMessage = ex.getMessage();
            ex.printStackTrace();
            return list;
        }
    }

    /**
     * 设置实体值
     *
     * @param type   数据类型
     * @param method 类函数
     * @param t      实体
     * @param name   名称
     * @param value  值
     */
    private void setModelValue(Type type, Method method, T t, String name, String value) {
        if (type == null || t == null || method == null || name == null || name.isEmpty())
            return;
        //验证值是否为空
        if (value == null)
            value = "";

        try {
            String typeName = type.toString();
            if (typeName == null || typeName.isEmpty())
                typeName = "";

            switch (typeName) {
                default:
                case "class java.lang.String":
                    method.invoke(t, value);
                    break;
                case "class java.lang.Integer":
                    if (value.isEmpty())
                        value = "0";
                    method.invoke(t, Integer.parseInt(value));
                    break;
                case "c java.lang.Boolean":
                    if (value.isEmpty())
                        value = "0";
                    method.invoke(t, Boolean.parseBoolean(value));
                    break;
                case "class java.util.Date":
                    if (value.isEmpty())
                        value = DateTimeUtil.geSpecifiedFormatTime("yyyy-MM-dd HH:mm:ss");
                    method.invoke(t, DateTimeUtil.getSpecifiedFormatDate(value));
                    break;
                case "class java.lang.Double":
                    if (value.isEmpty())
                        value = "0";
                    method.invoke(t, Double.parseDouble(value));
                    break;
            }
//        } catch (NoSuchMethodException e) {
//            e.printStackTrace();
//            ErrorMessage += e.getMessage();
        } catch (SecurityException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        }
    }

    /**
     * 读取实体指定属性值
     *
     * @param type   数据类型
     * @param method 类函数
     * @param t      实体
     * @return 返回 String
     */
    private String getModelValue(Type type, Method method, T t) {
        if (type == null || t == null || method == null)
            return "";
        try {
            Object obj = method.invoke(t);
            if (obj == null)
                obj = "";

            return obj.toString();
        } catch (SecurityException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            errorMessage += e.getMessage();
        }
        return "";
    }

    /**
     * excel 读取标题行
     *
     * @param headerRow 表头行
     * @return 返回 list
     */
    private List<String> readExcelHeader(Row headerRow) {
        List<String> list = new ArrayList<>();
        if (headerRow == null)
            return list;
        //读取有多少例
        int cellNum = headerRow.getLastCellNum();
        for (int i = 0; i < cellNum; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null)
                continue;
            //读取值
            String value = readCellDataType(cell).toString();
            if (value.isEmpty())
                continue;
            //添加标题
            list.add(value);
        }
        return list;
    }

    /**
     * List 读取标题行
     *
     * @param fields 实体属性集合
     * @return 返回 list
     */
    private List<String> readListHeader(Field[] fields) {
        List<String> header = new ArrayList<>();
        for (Field field :
                fields) {
            FieldMeta meta = field.getAnnotation(FieldMeta.class);
            String des = meta.description();
            if (des == null || des.isEmpty())
                continue;
            header.add(des);
        }
        //返回结果
        return header;
    }

    /**
     * 读取 Excle 数据类型
     *
     * @param cell 单元格
     * @return 返回 Object
     */
    private Object readCellDataType(Cell cell) {
        if (cell == null)
            return "";
        Object obj;
        CellType cellType = cell.getCellTypeEnum();
//        CellType cellType = cell.getCellType();
        switch (cellType) {
            //数字
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            case FORMULA:
            case BLANK:
            case ERROR:
            default:
                cell.setCellType(CellType.STRING);
                obj = cell.getStringCellValue();
                break;
        }
        //验证是否为 null
        obj = obj == null ? "" : obj;

        //返回结果
        return obj;
    }

    /**
     * 写入
     * 类型及列不能为空
     *
     * @param cell  单元格
     * @param type  数据类型
     * @param value 定入值
     */
    private void setCellDataType(Cell cell, Type type, String value) {
        //类型及列不能为空
        if (type == null || cell == null)
            return;
        //写入默认值
        if (value == null)
            value = "";
        //读取类型值
        String typeName = type.getTypeName();
        if (typeName == null)
            typeName = "";
        switch (typeName.toLowerCase()) {
            case "int":
            case "integer":
            case "double":
            case "float":
                if (value.isEmpty())
                    value = "0";
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.valueOf(value));
                break;
            case "boolean":
                if (value.isEmpty())
                    value = "0";
                cell.setCellType(CellType.BOOLEAN);
                cell.setCellValue(Boolean.valueOf(value));
                break;
            default:
                cell.setCellType(CellType.STRING);
                cell.setCellValue(value);
                break;
        }
    }
}
